﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity.Validation;

namespace DataAccessLayer
{
    public class ApproveRequisitionDAL
    {
        StationeryInventoryEntities1 ent;
        public ApproveRequisitionDAL()
        {
            ent = new StationeryInventoryEntities1();

        }

        public int getRoleInfoByUserName(String userName)
        {
            User u = ent.Users.FirstOrDefault(x => x.UserName == userName);
            return u.RoleID;
        }

        public List<Employee> getEmpListByUserName(String userName)
        {
            Employee e = ent.Employees.FirstOrDefault(x => x.User.UserName == userName);
            var empList = (from x in ent.Employees where x.DeptID == e.DeptID select x);
            return empList.ToList();
        }

        public String getEmpNameByUserName(String userName)
        {
            Employee e = ent.Employees.FirstOrDefault(x => x.User.UserName == userName);
            return e.EmployeeName;
        }

        public string getEmpNameByID(int empID)
        {
            Employee e = ent.Employees.FirstOrDefault(x => x.EmployeeID == empID);
            return e.EmployeeName;
        }

        public List<Requisition> getRequisitionByUserName(String userName)
        {
            Employee e = ent.Employees.FirstOrDefault(x => x.User.UserName == userName);
            var reqList = (from x in ent.Requisitions where x.RequestedBy == e.EmployeeID select x);
            return reqList.ToList();
        }

        //for viewing detail of each requisition
        public List<object> getReqDetailOneByReqID(int reqID)
        {
            var reqDetList = (from x in ent.RequisitionDetailOnes where x.ReqID == reqID select new { x.Stationery.ItemName, x.Stationery.Category.CategoryName, x.RequestQty });
            List<object> reqDetLst = reqDetList.ToList<object>();

            return reqDetLst;
        }

        public List<object> getAllPendingRequisitionByDept(String userName)
        {
            Employee e = ent.Employees.FirstOrDefault(x => x.User.UserName == userName);
            List<Employee> EmpList = (from x in ent.Employees where x.DeptID == e.DeptID select x).ToList();
            List<object> allReqList = new List<object>();

            for (int i = 0; i < EmpList.Count; i++)
            {
                int empId = EmpList[i].EmployeeID;
                var reqList = (from x in ent.Requisitions where x.RequestedBy == empId && x.ApprovalStatus == "Pending" select new { x.ReqID, x.RequestedBy, x.Employee.EmployeeName, x.ReqDate, x.ApprovalStatus });
                List<object> reqLst = new List<object>();
                reqLst = reqList.ToList<object>();

                for (int j = 0; j < reqLst.Count; j++)
                {
                    allReqList.Add(reqLst[j]);
                }
            }
            return allReqList;
        }

        public List<object> getPendingReqByUserName(int empId)
        {
            var reqList = (from x in ent.Requisitions where x.RequestedBy == empId && x.ApprovalStatus == "Pending" select new { x.ReqID, x.RequestedBy, x.Employee.EmployeeName, x.ReqDate, x.ApprovalStatus });
            List<object> reqLst = reqList.ToList<object>();
            return reqLst;
        }

        //to get all the employee list of pending requistion for each department
        public List<Employee> getPendingEmpListForDept(String userName)
        {
            Employee emp = ent.Employees.FirstOrDefault(x => x.User.UserName == userName);

            List<Employee> empList = (from e in ent.Employees
                                      join r in ent.Requisitions
                                      on e.EmployeeID equals r.RequestedBy
                                      where r.ApprovalStatus == "Pending"
                                      && e.DeptID == emp.DeptID
                                      select e).Distinct().ToList();

            return empList;
        }

        //for saving to deptrequisition and deptRequisitionDetail
        //**********************************************************//
        public String getDeptIDByEmpID(int empID)
        {
            Employee emp = ent.Employees.FirstOrDefault(x => x.EmployeeID == empID);
            return emp.DeptID;
        }
        //this method is to check the deptID with the value "null" for start data and end date
        public int checkDeptID(string deptID)
        {
            try
            {
                DeptRequisition dr = ent.DeptRequisitions.FirstOrDefault(x => x.DeptID == deptID && x.StartDate == null && x.EndDate == null);
                return dr.DeptReqID;
            }
            catch (Exception ex)
            {
                return 0;
            }

        }

        //to save to deptRequistion
        public int saveToDeptReq(String deptID)
        {
            DeptRequisition dr = new DeptRequisition();
            dr.DeptID = deptID;
            ent.DeptRequisitions.Add(dr);
            ent.SaveChanges();

            return dr.DeptReqID;
        }

        //for viewing detial of each requisition for saving to deptRequisition
        public List<RequisitionDetailOne> getRealReqDetailOneByReqID(int reqID)
        {
            var reqDetList = (from x in ent.RequisitionDetailOnes where x.ReqID == reqID select x);
            List<RequisitionDetailOne> reqDetLst = reqDetList.ToList();

            return reqDetLst;
        }


        public List<DeptRequisitionDetail> getDeptReqDetList(int deptReqID)
        {
            List<DeptRequisitionDetail> d = (from x in ent.DeptRequisitionDetails where x.DeptReqID == deptReqID select x).ToList();
            return d;
        }


        public void saveDeptReqDet(String itemCode, int reqQty, int deptReqID)
        {

            DeptRequisitionDetail d = new DeptRequisitionDetail();
            d.ItemCode = itemCode;
            d.NewRequestQty = reqQty;
            d.DeptReqID = deptReqID;
            ent.DeptRequisitionDetails.Add(d);
            ent.SaveChanges();



        }
        //to update the quantity of existing item
        public void updateDeptReqDet(String itemCode, int reqQty, int deptReqID)
        {
            DeptRequisitionDetail d = ent.DeptRequisitionDetails.FirstOrDefault(x => x.ItemCode == itemCode);
            d.NewRequestQty += reqQty;
            ent.SaveChanges();

        }

        //to update approval status in Requisition
        public void updateRequistionStatus(String deptID, int reqID)
        {

            Department d = ent.Departments.FirstOrDefault(x => x.DeptID == deptID);
            Requisition r = ent.Requisitions.FirstOrDefault(x => x.ReqID == reqID);
            r.ApprovalBy = d.DeptHeadID;
            r.ApprovalDate = DateTime.Today;
            r.ApprovalStatus = "Approved";
            ent.SaveChanges();

        }

        public void updateReqDetOneStatus(int reqDetID, String status, String reason)
        {
            RequisitionDetailOne ro = ent.RequisitionDetailOnes.FirstOrDefault(x => x.ReqDetailOneID == reqDetID);
            ro.ApprovalStatus = status;
            ro.Reason = reason;

            ent.SaveChanges();

        }
        //**********************************************************//

        //for sending notification to employee
        public Employee getEmpInfoByEmpID(int empId)
        {
            Employee emp = ent.Employees.FirstOrDefault(x => x.EmployeeID == empId);
            return emp;
        }
        public String getHeadEmail(string deptId)
        {
            Department d = ent.Departments.FirstOrDefault(x => x.DeptID == deptId);
            Employee e = ent.Employees.FirstOrDefault(x => x.EmployeeID == d.DeptHeadID);
            return e.Email;
        }
    }
}
